IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[cms_sp_PageSave]') AND type in (N'P', N'PC'))
	DROP PROCEDURE [dbo].[cms_sp_PageSave]
GO

create procedure [dbo].[cms_sp_PageSave]
( 
@PageId int,
@SiteGroupId int = null,
@SiteId int = null,
@SystemLanguageId int = null,
@TemplateId int,
@WorkflowId uniqueidentifier = null,
@IsLatestCompletedRevision bit = null,
@ParentPageId int = null,
@PageBehaviourTypeKey int,
@PageName nvarchar(150),
@PageUrl nvarchar(250),
@IsVisible bit,
@UseFileCache bit,
@PageTitle nvarchar(max) = null,
@PageTitleOverride nvarchar(150) = null,
@PageDescription nvarchar(max) = null,
@PageKeywords nvarchar(max) = null,
@IsPublished bit = null,
@PublishStartDate datetime = null,
@PublishEndDate datetime = null,
@LastUpdatedBy int,
@DateLastUpdated datetime,
@CreatedBy int,
@DateCreated datetime,
@PageTypeParameter nvarchar(150),
@ContentTypeParameter nvarchar(150),
@PageContentTypeParameter nvarchar(150),
@PageControlTypeParameter nvarchar(150)
)
as
declare @NewPageId int
declare @PageNodeId hierarchyid
declare @PublicPageId int
declare @PageTypeKey int
declare @IsLatestRevision bit
declare @IsDeleted bit
if @IsPublished is null
begin
	set @IsPublished = 1
end
set @IsDeleted = 0
set @IsLatestRevision = 1 
if @IsLatestCompletedRevision is null
begin
	set @IsLatestCompletedRevision = @IsLatestRevision
end
select 
	@PageNodeId = PageNodeId,
	@PublicPageId = PublicPageId,
	@PageTypeKey = PageTypeKey
from dbo.Pages
where PageId = @PageId
insert into dbo.Pages
(
SiteGroupId,
SiteId,
SystemLanguageId,
TemplateId,
PageNodeId,
PublicPageId,
PageTypeKey,
PageBehaviourTypeKey,
PageName,
PageUrl,
IsVisible,
UseFileCache,
PageTitle,
PageTitleOverride,
PageDescription,
PageKeywords,
CreatedBy,
DateCreated,
LastUpdatedBy,
DateLastUpdated
)
values
(
@SiteGroupId,
@SiteId,
@SystemLanguageId,
@TemplateId,
@PageNodeId,
@PublicPageId,
@PageTypeKey,
@PageBehaviourTypeKey,
@PageName,
@PageUrl,
@IsVisible,
@UseFileCache,
@PageTitle,
@PageTitleOverride,
@PageDescription,
@PageKeywords,
@CreatedBy,
@DateCreated,
@LastUpdatedBy,
@DateLastUpdated
)
set @NewPageId = SCOPE_IDENTITY()
update dbo.EntityRevisions
	set IsLatestRevision = 0
where PublicEntityId = @PublicPageId and 
	EntityTypeParameter = @PageTypeParameter
if @IsLatestCompletedRevision = 1
begin
	update dbo.EntityRevisions
		set IsLatestCompletedRevision = 0
	where PublicEntityId = @PublicPageId and 
		EntityTypeParameter = @PageTypeParameter
end
insert into dbo.EntityRevisions
(
EntityId,
PublicEntityId,
EntityTypeParameter,
WorkflowId,
IsLatestRevision,
IsLatestCompletedRevision,
IsDeleted,
IsPublished, 
PublishStartDate,
PublishEndDate
)
values
(
@NewPageId,
@PublicPageId,
@PageTypeParameter,
@WorkflowId,
@IsLatestRevision,
@IsLatestCompletedRevision,
@IsDeleted,
@IsPublished,
@PublishStartDate,
@PublishEndDate
)
-- copy all page's properties
declare @EntityMetaFieldLinkId int
declare @EntityMetaValue nvarchar(max)
declare page_property_cursor cursor for 
select
	EntityMetas.EntityMetaFieldLinkId,
	EntityMetas.EntityMetaValue
from dbo.EntityMetas
inner join dbo.EntityMetaFieldLinks
	on EntityMetaFieldLinks.EntityMetaFieldLinkId = EntityMetas.EntityMetaFieldLinkId and
		EntityMetaFieldLinks.EntityTypeParameter = @PageTypeParameter
where EntityId = @PageId
open page_property_cursor;
fetch next from page_property_cursor 
	into @EntityMetaFieldLinkId, @EntityMetaValue
while @@fetch_status = 0
begin
	insert into dbo.EntityMetas
	(
	EntityMetaFieldLinkId,
	EntityId,
	EntityMetaValue
	)
	values
	(
	@EntityMetaFieldLinkId,
	@NewPageId,
	@EntityMetaValue
	)
fetch next from page_property_cursor 
	into @EntityMetaFieldLinkId, @EntityMetaValue
end
close page_property_cursor
deallocate page_property_cursor
-- copy all page's controls
declare @PageControlId int
declare @TemplateRegionId int
declare @ModuleId int
declare @PageControlTypeKey int
declare @PageControlContentId int
declare @PageControlViewValue xml
declare @PageControlModelValue xml
declare @PageControlOrder int
declare @ContentId int
declare @ContentTypeId int
select 
	@ContentTypeId = ContentTypeId 
from [dbo].[ContentTypes]
where ContentTypeName = @PageContentTypeParameter
declare page_control_cursor cursor for 
select 
	PageControlId,	
	TemplateRegionId,
	ModuleId,
	PageControlTypeKey,
	PageControlContentId,
	PageControlViewValue,
	PageControlModelValue,
	PageControlOrder
from dbo.PageControls
inner join dbo.EntityRevisions
	on EntityRevisions.EntityId = PageControls.PageControlId and 
	EntityRevisions.EntityTypeParameter = @PageControlTypeParameter and
	EntityRevisions.IsLatestRevision = 1
where PageId = @PageId
open page_control_cursor;
fetch next from page_control_cursor 
	into 
		@PageControlId,		
		@TemplateRegionId,
		@ModuleId,
		@PageControlTypeKey,
		@PageControlContentId,
		@PageControlViewValue,
		@PageControlModelValue,
		@PageControlOrder
while @@fetch_status = 0
begin
	if @PageControlTypeKey = 1 -- page content
	begin
		insert into dbo.Contents
		(
		SiteId,
		SiteGroupId,
		SystemLanguageId,
		ContentTypeId,
		PublicContentId,
		ContentTitle,
		ContentBody,
		CreatedBy,
		DateCreated
		)
		select 
			@SiteId,
			@SiteGroupId,
			@SystemLanguageId,
			@ContentTypeId,
			0,
			ContentTitle,
			ContentBody,
			@LastUpdatedBy,
			@DateLastUpdated
		from dbo.Contents
		where ContentId = @PageControlContentId
		set @ContentId = SCOPE_IDENTITY()
		update dbo.Contents
			set PublicContentId = @ContentId
		where ContentId = @ContentId
		insert into dbo.EntityRevisions
		(
		EntityId,
		PublicEntityId,
		EntityTypeParameter,
		WorkflowId,
		IsLatestRevision,
		IsLatestCompletedRevision,
		IsDeleted,
		IsPublished
		)
		values
		(
		@ContentId,
		@ContentId,
		@ContentTypeParameter,
		@WorkflowId,
		@IsLatestRevision,
		@IsLatestCompletedRevision,
		@IsDeleted,
		@IsPublished
		)
	end
	insert into dbo.PageControls
	(
	PageId,
	TemplateRegionId,
	ModuleId,
	PageControlContentId,
	PageControlTypeKey,
	PageControlViewValue,
	PageControlModelValue,
	PageControlOrder,
	CreatedBy,
	DateCreated
	)
	values
	( 
	@NewPageId,
	@TemplateRegionId,
	@ModuleId,
	(case @PageControlTypeKey
		when 1 then @ContentId
		else @PageControlContentId
	end),
	@PageControlTypeKey,
	@PageControlViewValue,
	@PageControlModelValue,
	@PageControlOrder,
	@LastUpdatedBy,
	@DateLastUpdated
	)
	set @PageControlId = SCOPE_IDENTITY()
	insert into dbo.EntityRevisions
	(
	EntityId,
	PublicEntityId,
	EntityTypeParameter,
	WorkflowId,
	IsLatestRevision,
	IsLatestCompletedRevision,
	IsDeleted,
	IsPublished
	)
	values
	(
	@PageControlId,
	@PageControlId,
	@PageControlTypeParameter,
	@WorkflowId,
	@IsLatestRevision,
	@IsLatestCompletedRevision,
	@IsDeleted,
	@IsPublished
	)
fetch next from page_control_cursor 
	into 
		@PageControlId,		
		@TemplateRegionId,
		@ModuleId,
		@PageControlTypeKey,
		@PageControlContentId,
		@PageControlViewValue,
		@PageControlModelValue,
		@PageControlOrder
end
close page_control_cursor
deallocate page_control_cursor

return @NewPageId
GO

